﻿using QuanLyKho.DTO;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace QuanLyKho.DAL
{
    class PhieuNhapDAO : DataProvider
    {
        public void insert(DTO.PhieuNhapDTO phieuNhapDTO)
        {
            string sqlString = "insert into PhieuNhap values('"
                + phieuNhapDTO.MaPhieu + "','"
                + phieuNhapDTO.MaHang + "','"
                + phieuNhapDTO.MaKho + "','"
                + phieuNhapDTO.MaNhaPhanPhoi + "','"
                + phieuNhapDTO.NgayNhap.ToShortDateString() + "','"
                + phieuNhapDTO.SoLuong + "','"
                + 0 + "')";
            ExecuteNonQuery(sqlString);
        }

        public ArrayList getListHangTrongPhieuNhapByMa(string maPhieu)
        {
            string sqlString = "SELECT HH.MaHang, HH.TenHang, PN.MaKho, K.TenKho, PN.MaNhaPhanPhoi, NPP.TenNhaPhanPhoi, PN.NgayNhap, PN.SoLuong"
                + " FROM PhieuNhap PN, HangHoa HH, Kho K, NhaPhanPhoi NPP"
                + " WHERE PN.MaPhieu = '" + maPhieu + "'" + " AND PN.MaHang = HH.MaHang"
                + " AND PN.MaKho = K.MaKho AND NPP.MaNhaPhanPhoi = PN.MaNhaPhanPhoi";
            adapter = new OleDbDataAdapter(sqlString, connection);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);

            ArrayList listHangHoa = new ArrayList();
            DataTable dt = dataSet.Tables[0];
            int i, n = dt.Rows.Count;
            for (i = 0; i < n; i++)
            {
                object obj = GetHangHoaXuatDTOFromDataRow(dt, i);
                listHangHoa.Add(obj);

            }

            return listHangHoa;
        }

        private object GetHangHoaXuatDTOFromDataRow(DataTable dt, int i)
        {
            HangHoaXuatDTO hangHoaXuatDTO = new HangHoaXuatDTO();
            hangHoaXuatDTO.MaHang = dt.Rows[i]["MaHang"].ToString();
            hangHoaXuatDTO.TenHang = dt.Rows[i]["TenHang"].ToString();
            hangHoaXuatDTO.MaKho = dt.Rows[i]["MaKho"].ToString();
            hangHoaXuatDTO.TenKho = dt.Rows[i]["TenKho"].ToString();
            hangHoaXuatDTO.MaNhaPhanPhoi = dt.Rows[i]["MaNhaPhanPhoi"].ToString();
            hangHoaXuatDTO.TenNhaPhanPhoi = dt.Rows[i]["TenNhaPhanPhoi"].ToString();
            hangHoaXuatDTO.SoLuong = Int32.Parse(dt.Rows[i]["SoLuong"].ToString());
            hangHoaXuatDTO.NgayNhap = DateTime.Parse(dt.Rows[i]["NgayNhap"].ToString());
            return hangHoaXuatDTO;
        }

        public int getSoLuongHangNhap(string maHang, string maKho, string maNhaPhanPhoi)
        {
            string sqlString = "SELECT SUM(SoLuong) FROM PhieuNhap WHERE "
                + "MaHang = '" + maHang 
                + "' AND MaKho = '" + maKho 
                + "' AND MaNhaPhanPhoi = '" + maNhaPhanPhoi + "'";
            int soLuong;
            try
            {
                soLuong = Int32.Parse(ExecuteScalar(sqlString).ToString());
            }
            catch (Exception ex)
            { 
                soLuong = 0;
            }
            return soLuong;
        }

        public ArrayList getListPhieuNhap()
        {
            string sqlString = "Select MaPhieu from PhieuNhap";
            adapter = new OleDbDataAdapter(sqlString, connection);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);
            ArrayList listPhieuNhap = ConvertDataSetToArrayList(dataSet);

            return listPhieuNhap;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            PhieuNhapDTO phieuNhapDTO = new PhieuNhapDTO();
            phieuNhapDTO.MaPhieu = dt.Rows[i]["MaPhieu"].ToString();
            //phieuNhapDTO.MaHang = dt.Rows[i]["MaHang"].ToString();
            //phieuNhapDTO.MaKho = dt.Rows[i]["MaKho"].ToString();
            //phieuNhapDTO.MaNhaPhanPhoi = dt.Rows[i]["MaNhaPhanPhoi"].ToString();
            //phieuNhapDTO.SoLuong = Int32.Parse(dt.Rows[i]["SoLuong"].ToString());
            //phieuNhapDTO.NgayNhap = DateTime.Parse(dt.Rows[i]["NgayNhap"].ToString());

            return phieuNhapDTO;
        }

        internal int getTongSoLuongHangNhap(string maHang)
        {
            string sqlString = "SELECT SUM(SoLuong) FROM PhieuNhap WHERE "
                + "MaHang = '" + maHang + "'";
            int soLuong;
            try
            {
                soLuong = Int32.Parse(ExecuteScalar(sqlString).ToString());
            }
            catch (Exception ex)
            {
                soLuong = 0;
            }
            return soLuong;
        }
    }
}
